﻿function Format-TSMQuery {    
    <#
        .SYNOPSIS
            Format query to send to TSM
                    
        .DESCRIPTION
            The Format-TSMQuery function builds a SQL query based on the provided arguments
        
        .PARAMETER Table
            The table to query
        
        .PARAMETER Where
            A hashtable containing "<column name>"="<value>"
        
        .PARAMETER OrderBy
            The column to order results by
        
        .PARAMETER Columns
            The columns to be returned

        .EXAMPLE
            Format-TSMData -Data $data -DefaultDisplaySet $defaultDisplay

        .NOTES
            Contributors:  David Seibel
            Last Revision Date: 06/04/2015
    #>
    [CmdletBinding()]
    Param(
        [Parameter(Position = 0, Mandatory = $true)]
        $Table,
        
        [Parameter(Position = 1)]
        $Where,
        
        [Parameter(Position = 2)]
        $OrderBy,

        [Parameter(Position = 3)]
        $Columns = "*"
    )

    $query = "SELECT $Columns FROM $Table"

    $firstTime = $true
    foreach ($w in $Where) {
        if ($w.Value -ne $null) {
            if ($w.Value -is [DateTime]) {
                $col = $w.Column
                $value = Get-Date $w.Value -f 'yyyy-MM-dd HH:mm:ss.ffffff'
            } elseif ($w.Value -is [string]) {
                $col = "UCASE($($w.Column))"
                $value = $w.Value.Replace("*", "%").ToUpper()
            } else {
                $col = $w.Column
                $value = $w.Value
            }
            if ($value -ne "") {                
                if ($firstTime) {
                    if ($w.Operator -ieq "IS") {
                        $query += " WHERE $col $($w.Operator) $value"
                    } else {
                        $query += " WHERE $col $($w.Operator) '$value'"
                    }
                    $firstTime = $false
                } else {
                    if ($w.Operator -ieq "IS") {
                        $query += " AND $col $($w.Operator) $value"
                    } else {
                        $query += " AND $col $($w.Operator) '$value'"
                    }
                }
            }
        }
    }
    
    if ($OrderBy -ne $null -and $OrderBy -ne "") {
        $query += " ORDER BY $OrderBy"
    }
    Write-Verbose "Query: $query"
    return $query
}